package kr.hkit.score_pjt.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.framwordk.dao.CloseDAOUtil;
import kr.hkit.framwordk.dao.LoadPropertyDAO;
import kr.hkit.score_pjt.DTO.StdInfo;

public class StdInfoDao {
	private Connection con;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	public void setCon(Connection con) {
		this.con = con;
	}

	public StdInfoDao(Connection con) {
		this.con = con;
	}

	public StdInfoDao() {}
	
	public void inserProduct(StdInfo dto) throws SQLException{
		String sql ="insert into stdinfo values(?,?,?,?,?)";
		pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, dto.getStdNum());
		pstmt.setString(2, dto.getStdName());
		pstmt.setInt(3, dto.getKorScore());
		pstmt.setInt(4, dto.getEngScore());
		pstmt.setInt(5, dto.getMathScore());
		pstmt.executeUpdate();
		CloseDAOUtil.close(pstmt);
	}
	
	public ArrayList<StdInfo> selectAllStdInfoLists() throws Exception{
		String sql = "select grade, rank, stdnum, stdname, kor, eng, math, total, avg from view_stdinfo";
		ArrayList<StdInfo> lists = new ArrayList<>();
		pstmt = con.prepareStatement(sql);
		rs = pstmt.executeQuery();
		StdInfo std = null;
		while(rs.next()){
			std = new StdInfo();
			std.setStdGrade(rs.getInt("grade")).setStdRank(rs.getInt("rank"));
			std.setStdNum(rs.getInt("stdnum")).setStdName(rs.getString("stdname"));
			std.setKorScore(rs.getInt("kor")).setKorScore(rs.getInt("eng")).setKorScore(rs.getInt("math"));
			std.setTotal(rs.getInt("total")).setAvg(rs.getInt("avg"));
			lists.add(std);
		}
		if(lists.size()==0) throw new Exception("학생정보가 없습니다.");
		return lists;
	}
	
	public void delecteAllStdInfo() throws SQLException{
		String sql = "delete from stdinfo";
		pstmt = con.prepareStatement(sql);
		pstmt.executeUpdate();
		CloseDAOUtil.close(rs, pstmt);
	}
	
	public String selectStdNamebyStdnum(int stdNum) throws Exception {
		String sql = "select stdname from stdcode where stdnum=?";
		pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, stdNum);
		rs = pstmt.executeQuery();
		if(rs.next()){
			return rs.getString(1);
		}
		else{
			throw new Exception("해당 학생 존재하지 않음");
		}
	}
}
